package com.db;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.chart.PieChart;
import javafx.scene.chart.XYChart;
import javafx.scene.chart.XYChart.Series;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.to.CategoryTo;
import com.to.CityTo;
import com.to.CustomerTo;
import com.to.ResultTo;
import com.to.StateTo;

public class DashBoardDao implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	Connection conn = null;
	
	public List<StateTo> getAllState(){
		List<StateTo> list = new ArrayList<StateTo>();
		StateTo to = null;
		try{
			conn = CreateConnection.getCon();
			Statement st = (Statement) conn.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM  master_state");
  
			while (res.next()) {
				to = new StateTo();
				to.setId(res.getInt("state_id"));
				to.setDesc(res.getString("state_desc"));
				list.add(to);
			}
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}
	
	public StateTo getStateById(int id){
		
		StateTo to = null;
		try{
			if(id > 0){
				conn = CreateConnection.getCon();
				
				PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_state where state_id = ?");
				st.setInt(1, id);
				ResultSet res = st.executeQuery();
	  
				while (res.next()) {
					to = new StateTo();
					to.setId(res.getInt("state_id"));
					to.setDesc(res.getString("state_desc"));
				}
				res.close();
				st.close();
				conn.close();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return to;
	}
	
	
	public List<CityTo> getAllCity(){
		List<CityTo> list = new ArrayList<CityTo>();
		CityTo to = null;
		try{
			conn = CreateConnection.getCon();
			Statement st = (Statement) conn.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM  master_city city,master_state state where city.state_id = state.state_id");
  
			while (res.next()) {
				to = new CityTo();
				to.setId(res.getInt("city_id"));
				to.setDesc(res.getString("city_desc"));
				to.getStateTo().setId(res.getInt("state_id"));
				to.getStateTo().setDesc(res.getString("state_desc"));
				to.setZipCode(res.getInt("zip_code"));
				list.add(to);
			}
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}
	
	public CityTo getCityById(int id){
		
		CityTo to = null;
		try{
			if(id > 0){
				conn = CreateConnection.getCon();
				
				PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_city where city_id = ? ");
				st.setInt(1, id);
				ResultSet res = st.executeQuery();
	  
				while (res.next()) {
					to = new CityTo();
					to.setId(res.getInt("city_id"));
					to.setDesc(res.getString("city_desc"));
					to.setZipCode(res.getInt("zip_code"));
					to.setStateTo(getStateById(res.getInt("state_id")));
				}
				res.close();
				st.close();
				conn.close();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return to;
	}
	
	
	public List<CityTo> getCitiesByStateId(int stateid){
		List<CityTo> list = new ArrayList<CityTo>();
		CityTo to = null;
		
		try{
			conn = CreateConnection.getCon();
			
			if(stateid > 0){
				PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_city city,master_state state " +
						" where city.state_id = state.state_id and city.state_id = ? ");
				st.setInt(1,stateid);
				ResultSet res = st.executeQuery();
				  
				while (res.next()) {
					to = new CityTo();
					to.setId(res.getInt("city_id"));
					to.setDesc(res.getString("city_desc"));
					to.getStateTo().setId(res.getInt("state_id"));
					to.getStateTo().setDesc(res.getString("state_desc"));
					to.setZipCode(res.getInt("zip_code"));
					list.add(to);
				}
				res.close();
				st.close();
			}
			 
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}

	public List<CustomerTo> getAllCutomer(){
		List<CustomerTo> list = new ArrayList<CustomerTo>();
		CustomerTo to = null;
		try{
			conn = CreateConnection.getCon();
			PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  cust_detail ");
				
			ResultSet res = st.executeQuery();
				  
			while (res.next()) {
				to = new CustomerTo();
				to.setCustId(res.getInt("cust_id"));
				to.setCustName(res.getString("cust_name"));
				to.setCityTo(getCityById(res.getInt("cust_city_id")));
				list.add(to);
			}
			res.close();
			st.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<CategoryTo> getAllProductCat(){
		List<CategoryTo> list = new ArrayList<CategoryTo>();
		CategoryTo to = null;
		try{
			conn = CreateConnection.getCon();
			PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_category ");
				
			ResultSet res = st.executeQuery();
				  
			while (res.next()) {
				to = new CategoryTo();
				to.setId(res.getInt("cat_id"));
				to.setDesc(res.getString("cat_desc"));
				list.add(to);
			}
			res.close();
			st.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	@SuppressWarnings("unchecked")
	public ObservableList getDataCatwise(String cat){
		@SuppressWarnings("rawtypes")
		ObservableList data = FXCollections.observableArrayList();
		try {
			double total = 0;
			conn = CreateConnection.getCon();
			String query1 = "  select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
					" where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
					"  group by so.product_id";
			PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
			st1.setString(1, cat);
			ResultSet res1 = st1.executeQuery();
			while (res1.next()) {
				total  = res1.getDouble(1);
			}
			res1.close();
			st1.close();
			//conn = CreateConnection.getCon();
			String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
					" where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
					"  group by so.product_id";
			PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
			st.setString(1, cat);
			ResultSet res = st.executeQuery();
			  
			while (res.next()) {
				data.add(new PieChart.Data(res.getString(1),(res.getDouble(2)*100)/total));

			}
			res.close();
			st.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return data;
		
		
	}
	
	
	public ObservableList<ResultTo> getCatResult(){
		 ObservableList<ResultTo> data = FXCollections.observableArrayList();
		
			try{
				conn = CreateConnection.getCon();
				String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
						" where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
						"  group by mc.cat_id";
				PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
				
				ResultSet res = st.executeQuery();
				  
				while (res.next()) {
					data.add(new ResultTo(res.getString(1),res.getDouble(2)));
				}
				res.close();
				st.close();
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			
			return data;
	}
	
	public ObservableList<ResultTo> getProdResult(String cat){
		 ObservableList<ResultTo> data = FXCollections.observableArrayList();
		
			try{
				conn = CreateConnection.getCon();
				String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
						" where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
						"  group by so.product_id";
				PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
				st.setString(1, cat);
				ResultSet res = st.executeQuery();
				  
				while (res.next()) {
					data.add(new ResultTo(res.getString(1),res.getDouble(2)));
				}
				res.close();
				st.close();
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			
			return data;
	}
	
	public XYChart.Series<String,Double> getBarChart(){
		XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
		try{
			conn = CreateConnection.getCon();
			String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
					" where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
					"  group by mc.cat_id";
			PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
			
			ResultSet res = st.executeQuery();
			  
			while (res.next()) {
				aSeries.getData().add(new XYChart.Data(res.getString(1),res.getDouble(2)));
			}
			res.close();
			st.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return aSeries;
		
	}
	
	public double getTotalSales(){
		double total = 0;
		try{
			conn = CreateConnection.getCon();
			String query = "select sum(so.net_price) from sales_order so ";
			PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
			
			ResultSet res = st.executeQuery();
			while (res.next()) {
				total = res.getDouble(1);
			}
			res.close();
			st.close();
			conn.close();
		
		}catch(SQLException e){
			e.printStackTrace();
		}
	return total;
	}

}
